﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.ComponentModel;

namespace FinalCRM.App_Code
{
    public class CustomerContactBusiness
    {
        public string CustomerID { get; set; }
        public string Title { get; set; }
        public string CustomerName { get; set; }
        public string OfficePhone { get; set; }
        public string Mobile { get; set; }
        public string Fax { get; set; }
        public string Email { get; set; }
        public string CustomerAddress { get; set; }
        public string CompanyName { get; set; }
        public string UserName { get; set; }
        public string AccountID { get; set; }

        public static void createCustomerContact(CustomerContactBusiness c)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Data Source=PHYUSINHTET-PC\\SQLEXPRESS;Initial Catalog=FCRMdb;Integrated security=true";
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "Insert into Contact(CustomerID,Title,CustomerName,OfficePhone,Mobile,Fax,Email,CustomerAddress,CompanyName,UserName,AccountID)Values(@CustomerID,@Title,@CustomerName,@OfficePhone,@Mobile,@Fax,@Email,@CustomerAddress,@CompanyName,@UserName,@AccountID)";
                comm.Parameters.AddWithValue("@CustomerID", c.CustomerID);
                comm.Parameters.AddWithValue("@Title", c.Title);
                comm.Parameters.AddWithValue("@CustomerName", c.CompanyName);
                comm.Parameters.AddWithValue("@OfficePhone", c.OfficePhone);
                comm.Parameters.AddWithValue("@Mobile", c.Mobile);
                comm.Parameters.AddWithValue("@Fax", c.Fax);
                comm.Parameters.AddWithValue("@Email", c.Email);
                comm.Parameters.AddWithValue("@CustomerAddress", c.CustomerAddress);
                comm.Parameters.AddWithValue("@CompanyName", c.CompanyName);
                comm.Parameters.AddWithValue("@UserName", c.UserName);
                comm.Parameters.AddWithValue("@AccountID", c.AccountID);
                
                int rows = comm.ExecuteNonQuery();
                if (rows < 0)
                {

                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }


        public static DataTable viewCustomerContact()
        {
            SqlConnection conn = null;
            DataTable dt = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Data Source=PHYUSINHTET-PC\\SQLEXPRESS;Initial Catalog=FCRMdb;Integrated security=true";
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from Contact";
                SqlDataReader dr = comm.ExecuteReader();
                dt = new DataTable();
                dt.Load(dr);

                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }


        public static void updateCustomerContact(CustomerContactBusiness b)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Data Source=PHYUSINHTET-PC\\SQLEXPRESS;Initial Catalog=FCRMdb;Integrated security=true";
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "Update Contact set CustomerID=@CustomerID," + "Title=@Title," + "CustomerName=@CustomerName," + "OfficePhone=@OfficePhone," + "Mobile=@Mobile," + "Fax=@Fax," + "Email=@Email," + "CustomerAddress=@CustomerAddress," + "AccountID=@AccountID where CompanyName=@CompanyName";
                comm.Parameters.AddWithValue("@CompanyName", b.CompanyName);
                comm.Parameters.AddWithValue("@CustomerID", b.CustomerID);
                comm.Parameters.AddWithValue("@Title", b.Title);
                comm.Parameters.AddWithValue("@CustomerName", b.CustomerName);
                comm.Parameters.AddWithValue("@OfficePhone", b.OfficePhone);
                comm.Parameters.AddWithValue("@Mobile", b.Mobile);
                comm.Parameters.AddWithValue("@Fax", b.Fax);
                comm.Parameters.AddWithValue("@Email", b.Email);
                comm.Parameters.AddWithValue("@CustomerAddress", b.CustomerAddress);
                comm.Parameters.AddWithValue("@AccountID", b.AccountID);

                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void deleteCustomerContacts(CustomerContactBusiness b)
        {
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Data Source=PHYUSINHTET-PC\\SQLEXPRESS;Initial Catalog=CustomerContacts;Integrated security=true";
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Contact WHERE UserName=@UserName";
                comm.Parameters.AddWithValue("@UserName", b.UserName);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }


    }
}